package br.fatec.ads.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.fatec.ads.connection.DataSource;
import br.fatec.ads.exception.ClienteException;
import br.fatec.ads.exception.DataBaseException;
import br.fatec.ads.exception.PerguntaException;
import br.fatec.ads.model.Area;
import br.fatec.ads.model.Cliente;
import br.fatec.ads.model.Pergunta;

public class ClienteDAO {
	private static ClienteDAO clienteDAO;
	private static String INCLUIR_CLIENTE = "EXEC P_CLIENTE_INCLUIR ?, ?, ?";
	private static String ALTERAR_CLIENTE = "EXEC P_CLIENTE_ALTERAR ?, ?, ?, ?";
	private static String EXCLUIR_CLIENTE = "EXEC P_CLIENTE_EXCLUIR ?, ?";
	private static String CONSULTAR_CLIENTE = "EXEC P_CLIENTE_CONSULTAR ?";
	private static String LISTAR_CLIENTE = "EXEC P_CLIENTE_LISTAR ?";
	
	public ClienteDAO() {
		super();
	}
	
	public static ClienteDAO getInstance(){
		if(clienteDAO == null){
			clienteDAO = new ClienteDAO();
		}
		
		return clienteDAO;
	}
	
    public void incluirCliente(Cliente cliente) throws ClienteException, DataBaseException{
        Connection connection = null;
        PreparedStatement query = null;

        try{
            try{
                connection = DataSource.getConnection();
                query = connection.prepareStatement(INCLUIR_CLIENTE);

                int i = 0;
                query.setString(++i, cliente.getNomeCliente());
                query.setString(++i, cliente.getDescCliente());
                query.setString(++i, cliente.getUsuario());
                
                query.executeUpdate();
            }catch(SQLException cause){
                throw new ClienteException("Problemas ao INSERIR o Cliente!", cause);
            }finally{
                DataSource.close(connection);
                DataSource.close(query);
            }
        } catch(DataBaseException cause){
            throw new DataBaseException(cause);
        }
    }
    
    public void alterarCliente(Cliente cliente) throws ClienteException, DataBaseException {
        Connection connection = null;
        PreparedStatement query = null;

        try{
            try{
                connection = DataSource.getConnection();
                query = connection.prepareStatement(ALTERAR_CLIENTE);

                int i = 0;
                query.setInt(++i, cliente.getIdCliente());
                query.setString(++i, cliente.getNomeCliente());
                query.setString(++i, cliente.getDescCliente());
                query.setString(++i, cliente.getUsuario());

                query.executeUpdate();
            }catch(SQLException cause){
                throw new ClienteException("Problemas ao ALTERAR o Cliente!", cause);
            }finally{
                DataSource.close(connection);
                DataSource.close(query);
            }
        }catch(DataBaseException cause){
            throw new DataBaseException(cause);
        }
    }
    
    public void excluirCliente(Cliente cliente) throws ClienteException, DataBaseException{
        Connection connection = null;
        PreparedStatement query = null;

        try{
            try{
                connection = DataSource.getConnection();
                query = connection.prepareStatement(EXCLUIR_CLIENTE);

                int i = 0;
                query.setInt(++i, cliente.getIdCliente());
                query.setString(++i, cliente.getUsuario());

                query.executeUpdate();
            }catch(SQLException cause){
                throw new ClienteException("Problemas ao EXCLUIR o Cliente!", cause);
            }finally{
                DataSource.close(connection);
                DataSource.close(query);
            }
        }catch(DataBaseException cause){
            throw new DataBaseException(cause);
        }
    }

    public Cliente getCliente(int idCliente) throws ClienteException, DataBaseException{
    	Connection connection = null;
    	PreparedStatement query = null;
    	ResultSet resultSet = null;
    	Cliente cliente = null;
    	
    	try{
    		try{
    			cliente = new Cliente();
    			
    			connection = DataSource.getConnection();
    			query = connection.prepareStatement(CONSULTAR_CLIENTE);
    			
    			int i = 0;
    			query.setInt(++i, idCliente);
    			
    			resultSet = query.executeQuery();
    			
    			while(resultSet.next()){
    				cliente.setIdCliente(resultSet.getInt("CLI_IDCLIENTE"));
    				cliente.setNomeCliente(resultSet.getString("CLI_NMCLIENTE"));
    				cliente.setDescCliente(resultSet.getString("CLI_DESCCLIENTE"));
    				cliente.setAtivo(resultSet.getString("CLI_ICATIVO"));
    				cliente.setDataHora(resultSet.getTimestamp("CLI_DTHRINCLUSAO"));
    				cliente.setUsuario(resultSet.getString("CLI_NMUSUARIOINCLUSAO"));
    			}
    		}catch (SQLException cause) {
    			throw new ClienteException("Problemas ao CONSULTAR o Cliente!", cause);
			}finally{
				DataSource.close(connection);
				DataSource.close(query);
				DataSource.close(resultSet);
			}
    	}catch (DataBaseException cause) {
    		throw new DataBaseException(cause);
		}
    	
    	return cliente;
    }
    
    public List listarCliente(Cliente cliente) throws ClienteException, DataBaseException{
    	Connection connection = null;
    	PreparedStatement query = null;
    	ResultSet resultSet = null;
    	List listaCliente = new ArrayList();
    	Cliente cli = null;
    	
    	try{
    		try{
    			connection = DataSource.getConnection();
    			query = connection.prepareStatement(LISTAR_CLIENTE);
    			
    			int i = 0;
    			query.setString(++i, cliente.getNomeCliente());
    			
    			resultSet = query.executeQuery();
    			
    			while(resultSet.next()){
    				cli = new Cliente();
    				
    				cli.setIdCliente(resultSet.getInt("CLI_IDCLIENTE"));
    				cli.setNomeCliente(resultSet.getString("CLI_NMCLIENTE"));
    				cli.setDescCliente(resultSet.getString("CLI_DESCCLIENTE"));
    				cli.setAtivo(resultSet.getString("CLI_ICATIVO"));
    				cli.setDataHora(resultSet.getTimestamp("CLI_DTHRINCLUSAO"));
    				cli.setUsuario(resultSet.getString("CLI_NMUSUARIOINCLUSAO"));
    				
    				listaCliente.add(cli);
    			}
    		}catch (SQLException cause) {
    			throw new ClienteException("Problemas ao LISTAR o Cliente!", cause);
			}finally{
				DataSource.close(connection);
				DataSource.close(query);
				DataSource.close(resultSet);
			}
    	}catch (DataBaseException cause) {
    		throw new DataBaseException(cause);
		}
    	
    	return listaCliente;
    }
}